TURBU Tech

The last few versions of MS SQL Server have included a feature where you can create new functions in .NET assemblies and register them with the database server. This can be a good way to deal with concepts that are difficult to express in T/SQL, and it can provide some big performance boosts. The process is not without its warts, though.

A few weeks ago, I found that the query for a major report in our system at work was spending a huge amount of time in the CPU, not the database itself.  Turns out we had some data that was encoded as a variable-length string, and a T/SQL function that decoded it into a list of numbers.  Since SQL doesn’t have any concept of arrays or array indexing, this involved a loop containing a call to the string copy function with a length of 1.  Ugly, no?

I decided to see if it could be done any quicker in a language with real array notation.  I wrote up a quick prototype in Prism that improved performance by about a factor of 5.  Unfortunately, for whatever reason, the DLL that holds our UDFs is in C#, so I had to translate it.  And it worked all right, right up until it fed it a string with a value of NULL.  Then I got a NullReferenceException, which is not something you want to see in the middle of your SQL query!

So I enclosed the string-processing logic in a block with “if (!input.IsNull)” at the top. That worked, and everyone was happy… right up until today.  Suddenly I’m getting NullReferenceExceptions from this routine again.  For various arcane reasons, you can’t debug this in any obvious way, such as loading up your source and attaching the Visual Studio debugger to the DLL with SQL Server as the host app, so it took some trial and error before I found that this was also coming from NULL strings, for some reason.  I ended up having to change the line at the top of the code block to “if ((input != null) && (!input.IsNull))” and now it works.  (I hope.)

Oh, and why didn’t I just call String.IsNullOrEmpty?  Because the input wasn’t a System.String.  This is coming from SQL Server, which has its own types for everything, so the input to the function is of type SqlString, which doesn’t have a IsNullOrEmpty method.

So, when is a null not a null?  Why, when it’s a different type of null, of course!  Makes me glad I do most of my work in Delphi, which has the good sense to treat null strings as empty strings.  KISS principle, y’know?